# import cx_Oracle
import pymysql
import datetime

class DB(object):
    """
            數據庫操作類
    """
    def __init__(self,user,password,host,port,dbname,sid=None):
        self.user = user
        self.password = password
        self.host = host
        self.port = port
        self.dbname = dbname
        self.sid = sid
        self.charset = 'utf8'
        self.conn = None
        self.cur = None

    def get_conn(self):
        if self.dbname == 'mysql':

            return pymysql.connect(host=self.host,port=self.port,user=self.user,password=self.password,db=self.sid,charset=self.charset)
        elif self.dbname == 'oracle':
            pass
            # return cx_Oracle.connect(self.user+'/'+self.password+'@'+self.host+':'+self.port+'/'+self.sid)


    def get_cur(self):
        """
           數據庫連接
        """
        try:
            self.conn = self.get_conn()
            self.cur = self.conn.cursor()
        except Exception as e:
            print('Cursor Error',e)

    def close(self):
        """
            關閉數據庫連接
        """
        self.cur.close()
        self.conn.close()

    def query(self,sql):
        """
            查詢
        """
        try:
            self.cur.execute(sql)
            data = self.cur.fetchall()
            return data
        except Exception as e:
            print('Query Error:',e)

    def insert(self,sql):
        """
            插入，更新
        """
        try:
            self.cur.execute(sql)
            self.conn.commit()
        except Exception as e:
            print('Insert Error',e)


    def callSP(self,spName,cursor):
        """
            Call 存儲過程
        """
        try:
            data = self.cur.callproc(spName,cursor)
            return data
        except Exception as e:
            print('SP Call Error:',e)

from wfs import models
def geshihua(sql):
    con = DB(user='user1',password='Beaconuser1!',host='10.129.7.200',port=3306,sid='db_st',dbname='mysql')
    con.get_cur()
    data = con.query(sql)
    content={}
    list1=[]
    list = []
    for i in data:
        content={}
        if i[1] in list:
            index = list.index(i[1])
            name = models.User.objects.filter(username=i[8]).values('last_name')[0]['last_name']
            if i[12] == '0':
                list1[index]['approver_name'].append(i[8]+'('+name+')')
        # elif i[12] == '0':
        #     content['card_id'] = i[0]
        #     content['id'] = i[1]
        #     content['name'] = i[2]
        #     content['apply'] = {'applicant_id': i[3], 'applicant_name': i[10]}
        #     content['wrecord_id'] = i[4]
        #     content['wrecord__create_time'] = i[5].strftime("%Y-%m-%d %H:%M:%S")
        #     content['post_type'] = i[6]
        #     content['approver_name'] = i[7]
        #     content['approver'] = i[8]

        else:

            name = models.User.objects.filter(username=i[8]).values('last_name')
            if name:
                name = i[8]+'('+name[0]['last_name']+')'
            content['approver_name'] =[]
            content['card_id']=i[0]
            content['id'] = i[1]
            content['name']=i[2]
            content['apply']={'applicant_id':i[3],'applicant_name':i[10]}
            content['wrecord_id']=i[4]
            content['wrecord__create_time']=i[5].strftime("%Y-%m-%d %H:%M:%S")
            content['post_type']=i[6]
            content['approver_name'] = [name] if i[12] == '0' else []
            content['approver']= i[8]
            if i[11]==0:
                content['action'] = '未簽核'
            else:
                content['action'] = '已簽核'

            if i[9]==None:

                content['wrecord__end_time'] = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")

                t = datetime.datetime.now() - i[5]

            else:
                content['wrecord__end_time'] = i[9].strftime("%Y-%m-%d %H:%M:%S")

                t = i[9] - i[5]

            sec = int(t.seconds)
            content['timestamp'] = sec
            hour = int(int(t.seconds) / 3600)
            # print(t)
            # print(t.strftime('%H:%M:%S'))
            minu = int((sec - hour * 3600) / 60)
            if hour == 0:
                content['time'] = str(minu) + '分鐘'
            else:
                content['time'] = str(hour) + '小時' + str(minu) + '分鐘'
            list1.append(content)
            if i[1] not in list:
                list.append(i[1])
    # print(list1)
    # index = 0
    # for i in list1:
    #     if 'card_id' not in i.keys():
    #             list1.remove(i)
    #     #         index +=1
    #     # else:
    #     #     index += 1

    return list1
    #     list1.append(content)
    # print(len(list1),list1)


